package cosplay.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cosplay.beans.Tags;

@Repository (value="tagdao")
public class TagDAOImpl implements TagDAO{
	private JdbcTemplate jt;
	@Autowired
	public TagDAOImpl(DataSource data)
	{
		this.jt=new JdbcTemplate(data);
	}

	@Override
	public int addSys(final Tags tag) {
		// TODO Auto-generated method stub
		int id=0;
		if(tag!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_ADDSYS(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setString(1, tag.getWord());
							stm.registerOutParameter(2, java.sql.Types.INTEGER);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getInt(2);
								obj[1]=rs;
								return obj;
							}});
			id=((Integer) obj[0]).intValue();
		}
		return id;
	}

	@Override
	public boolean delSys(final int id) {
		// TODO Auto-generated method stub
		boolean sucess=false;
		if(id>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_DELSYS(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, id);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public boolean edit(final Tags tag, int id) {
		// TODO Auto-generated method stub
		if (id<0)
			return false;
		boolean sucess=false;
		if(tag!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_EDIT(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setBoolean(1, tag.isSystem());
							stm.setString(2, tag.getWord());;
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(3);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@Override
	public boolean editSys(final Tags tag, int id) {
		// TODO Auto-generated method stub
		if (id<0)
			return false;
		boolean sucess=false;
		if(tag!=null)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_EDITSYS(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setBoolean(1, tag.isSystem());
							stm.setString(2, tag.getWord());;
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(3);
								obj[1]=rs;
								return obj;
							}});
			sucess=((Boolean) obj[0]).booleanValue();
		}
		return sucess;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Tags> getAll() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL TAG_GETALL(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Tags> tags=new ArrayList<Tags>();
							while (rs.next())
							{
								Tags tag =new Tags();
								tag.setId(rs.getInt("id"));
								tag.setSystem(rs.getBoolean("systemword"));
								tag.setWord(rs.getString("words"));
								tags.add(tag);
							}
							obj[1]=tags;
							return obj;
						}});
		return (List<Tags>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Tags> getAllSys() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL TAG_GETALLSYS(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Tags> tags=new ArrayList<Tags>();
							while (rs.next())
							{
								Tags tag =new Tags();
								tag.setId(rs.getInt("id"));
								tag.setSystem(rs.getBoolean("systemword"));
								tag.setWord(rs.getString("words"));
								tags.add(tag);
							}
							obj[1]=tags;
							return obj;
						}});
		return (List<Tags>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Tags> getByPhoto(final int photoid) {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL TAG_GETALL(?,?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.setInt(1, photoid);
						stm.registerOutParameter(2, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Tags> tags=new ArrayList<Tags>();
							while (rs.next())
							{
								Tags tag =new Tags();
								tag.setId(rs.getInt("id"));
								tag.setSystem(rs.getBoolean("systemword"));
								tag.setWord(rs.getString("words"));
								tags.add(tag);
							}
							obj[1]=tags;
							return obj;
						}});
		return (List<Tags>) obj[1];
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Tags> getAllForAdmin() {
		// TODO Auto-generated method stub
		Object[] obj=(Object[]) jt.execute(
				new CallableStatementCreator(){

					@Override
					public CallableStatement createCallableStatement(
							Connection conn) throws SQLException {
						// TODO Auto-generated method stub
						String sql="CALL TAG_GETALLFORADMIN(?);";
						CallableStatement stm=conn.prepareCall(sql);
						stm.registerOutParameter(1, java.sql.Types.INTEGER);
						return stm;
					}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getInt(1);
							obj[1]=null;
							List<Tags> tags=new ArrayList<Tags>();
							while (rs.next())
							{
								Tags tag =new Tags();
								tag.setId(rs.getInt("id"));
								tag.setSystem(rs.getBoolean("systemword"));
								tag.setWord(rs.getString("words"));
								tags.add(tag);
							}
							obj[1]=tags;
							return obj;
						}});
		return (List<Tags>) obj[1];
	}

	@Override
	public boolean appendById(final int tagid, final int photoid) {
		// TODO Auto-generated method stub
		if((photoid>0)&&(tagid>0))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_APPENDBYID(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.setInt(2, tagid);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean appendByName(final String tagword, final int photoid) {
		// TODO Auto-generated method stub
		if((photoid>0)&&(tagword!=null))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_APPENDBYNAME(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.setString(2, tagword);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean removeById(final int tagid, final int photoid) {
		// TODO Auto-generated method stub
		if((photoid>0)&&(tagid>0))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_REMOVEBYID(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.setInt(2, tagid);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public boolean removeByName(final String tagword, final int photoid) {
		// TODO Auto-generated method stub
		if((photoid>0)&&(tagword!=null))
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_REMOVEBYNAME(?,?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, photoid);
							stm.setString(2, tagword);
							stm.registerOutParameter(3, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

						@Override
						public Object doInCallableStatement(
								CallableStatement stm) throws SQLException,
								DataAccessException {
							// TODO Auto-generated method stub
							ResultSet rs=stm.executeQuery();
							Object[] obj=new Object[2];
							obj[0]=stm.getBoolean(3);
							obj[1]=rs;
							return obj;
						}});
				return ((Boolean) obj[0]).booleanValue();
		}
		return false;
	}

	@Override
	public Tags get(final int tid) {
		// TODO Auto-generated method stub
		if(tid>0)
		{
			Object[] obj=(Object[]) jt.execute(
					new CallableStatementCreator(){

						@Override
						public CallableStatement createCallableStatement(
								Connection conn) throws SQLException {
							// TODO Auto-generated method stub
							String sql="CALL TAG_GET(?,?);";
							CallableStatement stm=conn.prepareCall(sql);
							stm.setInt(1, tid);
							stm.registerOutParameter(2, java.sql.Types.BOOLEAN);
							return stm;
						}}, new CallableStatementCallback(){

							@Override
							public Object doInCallableStatement(
									CallableStatement stm) throws SQLException,
									DataAccessException {
								// TODO Auto-generated method stub
								ResultSet rs=stm.executeQuery();
								Object[] obj=new Object[2];
								obj[0]=stm.getBoolean(2);
								obj[1]=null;
								Tags tag =new Tags();
								if (rs.next())
								{
									tag.setId(rs.getInt("id"));
									tag.setSystem(rs.getBoolean("systemword"));
									tag.setWord(rs.getString("words"));
								}
								obj[1]=tag;
								return obj;
							}});
			if(((Boolean) obj[0]).booleanValue())
				return (Tags) obj[1];
		}
		return null;
	}

}
